CREATE PROCEDURE p1 as 
BEGIN
DECLARE @a int;
SELECT @a = 123;
PRINT @a;
END
GO

CREATE TABLE t1 ( b int) ;
GO

INSERT INTO t1 VALUES (1)
GO

INSERT INTO t1 VALUES (2)
GO

INSERT INTO t1 VALUES (3)
GO

CREATE PROCEDURE p2 as 
BEGIN
DECLARE @a int;
SELECT @a = b from t1 where (b = 1);
PRINT @a;
END
GO

EXEC p1;
GO

EXEC p2;
GO

DROP TABLE t1
GO

DROP PROCEDURE p1
GO

DROP PROCEDURE p2
GO

